{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## QQ3\n",
    "Lets dive a little into the math behind the Power Law just a little. Do not freak out if you are a little rusty on the math; remember, this is just a Quick Question - not a Graded Assignment! These are here to stretch you just a little!\n",
    "\n",
    "OK, in the lesson you learned that very often the frequency of SKU sales follow what is called the 'power law', given by the equation y=a∗xk, where: y is the (cumulative) percentage of items sold, x is the percent of products, and both a and k are constants selected to fit the real data to the power law curve. Consider the example of a store where 5% of the products account for 66.6% of the items sold, and 50% of the products account for 95% of the items sold.\n",
    "\n",
    "Calculate the values of a and k that would fit a power law curve to these data.\n",
    "\n",
    "(Relax if your math is a little rusty! We will help you through this. As a hint, remember that ln(axb)=ln(a)+b∗ln(x) where ln is the natural logarithm. In spreadsheets, the function is LN[x], so that LN[50] = 3.912 and since 2∗52=50, we can see that LN[50]=LN[2]+2*LN[5] = 3.912 as well!)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1.05720034044399, 0.154249376118547)]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sympy import Symbol\n",
    "from sympy.solvers import solve\n",
    "a = Symbol('a')\n",
    "k = Symbol('k')\n",
    "solve([0.666 - a*0.05**k , 0.95 - a*0.5**k], a, k)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## QQ4\n",
    "0 points possible (ungraded)\n",
    "Smalltown Bikes, a local retailer of spare parts for bicycles, carries only a dozen different SKUs. Nevertheless, managing their inventory has become complicated and the company has decided to implement an ABC segmentation strategy. The company has asked for your help in segmenting their inventory.\n",
    "\n",
    "The company has decided to do this segmentation based on the revenue, using 75% of the value as threshold between types A and B, and 95% of the value as threshold between types B and C. In the linked file (W1L1QQ4.xlsx), you will find the information you need regarding the number of parts sold by SmallTown Bikes and their price.\n",
    "\n",
    "How many SKUs of the dozen carried by Smalltown Bikes fall in the A category?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "height has been deprecated.\n",
      "\n",
      "line_width has been deprecated, use display.width instead (currently both are\n",
      "identical)\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/a59347/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:2: FutureWarning: \n",
      "mpl_style had been deprecated and will be removed in a future version.\n",
      "Use `matplotlib.pyplot.style.use` instead.\n",
      "\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "pd.reset_option('all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data = pd.read_excel(\"W1L1QQ4.xlsx\", names=['part_number', 'unit_price', 'units_sold'])\n",
    "data['extended_value'] = data['unit_price'] * data['units_sold']\n",
    "data.sort_values(by=['extended_value'], ascending=False, inplace=True)\n",
    "data['extended_value_cum'] = data['extended_value'].cumsum()\n",
    "data['percentile'] = data['extended_value_cum'] / data['extended_value'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>part_number</th>\n",
       "      <th>unit_price</th>\n",
       "      <th>units_sold</th>\n",
       "      <th>extended_value</th>\n",
       "      <th>extended_value_cum</th>\n",
       "      <th>percentile</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>SKU78216</td>\n",
       "      <td>1144.76</td>\n",
       "      <td>232</td>\n",
       "      <td>265584.32</td>\n",
       "      <td>265584.32</td>\n",
       "      <td>0.338219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SKU62086</td>\n",
       "      <td>263.98</td>\n",
       "      <td>510</td>\n",
       "      <td>134629.80</td>\n",
       "      <td>400214.12</td>\n",
       "      <td>0.509669</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>SKU65859</td>\n",
       "      <td>1217.41</td>\n",
       "      <td>87</td>\n",
       "      <td>105914.67</td>\n",
       "      <td>506128.79</td>\n",
       "      <td>0.644550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>SKU73835</td>\n",
       "      <td>826.79</td>\n",
       "      <td>68</td>\n",
       "      <td>56221.72</td>\n",
       "      <td>562350.51</td>\n",
       "      <td>0.716148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>SKU72812</td>\n",
       "      <td>444.16</td>\n",
       "      <td>110</td>\n",
       "      <td>48857.60</td>\n",
       "      <td>611208.11</td>\n",
       "      <td>0.778368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SKU60496</td>\n",
       "      <td>71.72</td>\n",
       "      <td>546</td>\n",
       "      <td>39159.12</td>\n",
       "      <td>650367.23</td>\n",
       "      <td>0.828236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SKU37892</td>\n",
       "      <td>97.08</td>\n",
       "      <td>357</td>\n",
       "      <td>34657.56</td>\n",
       "      <td>685024.79</td>\n",
       "      <td>0.872373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>SKU86637</td>\n",
       "      <td>57.81</td>\n",
       "      <td>476</td>\n",
       "      <td>27517.56</td>\n",
       "      <td>712542.35</td>\n",
       "      <td>0.907416</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>SKU98190</td>\n",
       "      <td>352.58</td>\n",
       "      <td>77</td>\n",
       "      <td>27148.66</td>\n",
       "      <td>739691.01</td>\n",
       "      <td>0.941989</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>SKU85162</td>\n",
       "      <td>55.89</td>\n",
       "      <td>388</td>\n",
       "      <td>21685.32</td>\n",
       "      <td>761376.33</td>\n",
       "      <td>0.969606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SKU15303</td>\n",
       "      <td>62.09</td>\n",
       "      <td>264</td>\n",
       "      <td>16391.76</td>\n",
       "      <td>777768.09</td>\n",
       "      <td>0.990480</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SKU12896</td>\n",
       "      <td>63.35</td>\n",
       "      <td>118</td>\n",
       "      <td>7475.30</td>\n",
       "      <td>785243.39</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   part_number  unit_price  units_sold  extended_value  extended_value_cum  \\\n",
       "8     SKU78216     1144.76         232       265584.32           265584.32   \n",
       "4     SKU62086      263.98         510       134629.80           400214.12   \n",
       "5     SKU65859     1217.41          87       105914.67           506128.79   \n",
       "7     SKU73835      826.79          68        56221.72           562350.51   \n",
       "6     SKU72812      444.16         110        48857.60           611208.11   \n",
       "3     SKU60496       71.72         546        39159.12           650367.23   \n",
       "2     SKU37892       97.08         357        34657.56           685024.79   \n",
       "10    SKU86637       57.81         476        27517.56           712542.35   \n",
       "11    SKU98190      352.58          77        27148.66           739691.01   \n",
       "9     SKU85162       55.89         388        21685.32           761376.33   \n",
       "1     SKU15303       62.09         264        16391.76           777768.09   \n",
       "0     SKU12896       63.35         118         7475.30           785243.39   \n",
       "\n",
       "    percentile  \n",
       "8     0.338219  \n",
       "4     0.509669  \n",
       "5     0.644550  \n",
       "7     0.716148  \n",
       "6     0.778368  \n",
       "3     0.828236  \n",
       "2     0.872373  \n",
       "10    0.907416  \n",
       "11    0.941989  \n",
       "9     0.969606  \n",
       "1     0.990480  \n",
       "0     1.000000  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# of Products Account for threshold: 4\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>part_number</th>\n",
       "      <th>unit_price</th>\n",
       "      <th>units_sold</th>\n",
       "      <th>extended_value</th>\n",
       "      <th>extended_value_cum</th>\n",
       "      <th>percentile</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>SKU78216</td>\n",
       "      <td>1144.76</td>\n",
       "      <td>232</td>\n",
       "      <td>265584.32</td>\n",
       "      <td>265584.32</td>\n",
       "      <td>0.338219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SKU62086</td>\n",
       "      <td>263.98</td>\n",
       "      <td>510</td>\n",
       "      <td>134629.80</td>\n",
       "      <td>400214.12</td>\n",
       "      <td>0.509669</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>SKU65859</td>\n",
       "      <td>1217.41</td>\n",
       "      <td>87</td>\n",
       "      <td>105914.67</td>\n",
       "      <td>506128.79</td>\n",
       "      <td>0.644550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>SKU73835</td>\n",
       "      <td>826.79</td>\n",
       "      <td>68</td>\n",
       "      <td>56221.72</td>\n",
       "      <td>562350.51</td>\n",
       "      <td>0.716148</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  part_number  unit_price  units_sold  extended_value  extended_value_cum  \\\n",
       "8    SKU78216     1144.76         232       265584.32           265584.32   \n",
       "4    SKU62086      263.98         510       134629.80           400214.12   \n",
       "5    SKU65859     1217.41          87       105914.67           506128.79   \n",
       "7    SKU73835      826.79          68        56221.72           562350.51   \n",
       "\n",
       "   percentile  \n",
       "8    0.338219  \n",
       "4    0.509669  \n",
       "5    0.644550  \n",
       "7    0.716148  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "threshold = 0.75\n",
    "print('# of Products Account for threshold:', data[data['percentile'] < threshold].shape[0])\n",
    "data[data['percentile'] < threshold]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## PP1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Practice Problem 1:  Uncertainty in Supply Chains\n",
    "Suppose that daily demand for bagels at the local coffee shop where you work is found to be Normally distributed with a mean of 250 and a standard deviation of 75 units. \n",
    "\n",
    "### Part 1\n",
    "\n",
    "0 points possible (ungraded)\n",
    "Suppose you have 350 bagels ready to sell on a certain day. What is the probability that you will run out? That is, what is the probability that the demand is greater than 350 on a certain day? (Enter your solution as a probability between 0 and 1 rather than as a percentage.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "mu = 250\n",
    "sigma = 75"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from scipy.stats import norm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.091211219725867876"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "p_instock = norm.cdf(350, loc=mu, scale=sigma)\n",
    "p_runout = 1 - p_instock\n",
    "p_runout"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The same problem can also be used based on the definition of normal distribution directly. Or we can transform and scale the problem to a standard normal distribution (Z-transform) and then look it up in the table. And I am going to use the lookup table to solve the Part 2."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Part 2\n",
    "0 points possible (ungraded)\n",
    "This is not good enough for you! How many bagels do you need to prepare to have only 5% probability of running out?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Standard Normal Distribution](https://en.wikipedia.org/wiki/Standard_normal_table)\n",
    "\n",
    "The probabilities listed are only the area between the mean (0) and value of Z. Since we will be looking at the right hand side of the distribution. We need to lookup the Z value which maps to a probability of (0.95 - 0.5) = 0.45. By looking at the cumulative table, we can easily identify that Z falls between 1.64 (0.44950), 1.65 (0.45053). \n",
    "\n",
    "$$Z = \\frac{x - \\mu}{\\sigma}$$\n",
    "\n",
    "$$x = Z * \\sigma + \\mu = 1.65 * 75 + 250 = 373$$"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is how you do that using [scipy.stats.norm.ppf](https://docs.scipy.org/doc/scipy-0.16.1/reference/generated/scipy.stats.norm.html#scipy-stats-norm) (percentage probability function, inverse of cumulative probability function)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "373.36402202136043"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "norm.ppf((1-0.05), loc=mu, scale=sigma)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Part 3\n",
    "0 points possible (ungraded)\n",
    "You want to be even surer that you do not run out of bagels! How many bagels do you need to prepare to have only 1% probability of running out?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "424.47609055306305"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "norm.ppf((1-0.01), loc=mu, scale=sigma)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## PP2: Three Month Salary Stores\n",
    " Bookmark this page\n",
    "Practice Problem 2: Three Month Salary Stores\n",
    "3-Month Salary (3MS) is a high-end retailer of engagement rings for the Greater Boston Region. They carry around 75 different types of engagement ring mountings and 25 different types of diamonds, for a total of 100 SKUs. They have traditionally kept their inventory in a vault in their flagship store in downtown Boston. However, as real estate prices rise and their sales volume increases, they are considering moving the safety inventory of all but the most profitable SKUs to a safe warehouse in the nearby town of Watertown, where real estate is somewhat cheaper. The company has decided to implement an ABC segmentation of their SKUs, and it has asked for your help.\n",
    "\n",
    "The company has decided to do this segmentation based on profitability: type A products are those that are most profitable, bringing the retailer a 34% of its monthly profit; type B products are those that bring the next 37% of the profit; and type C are the rest. In the linked file (w1pp3_sku3ms.xls), you will find the information you need regarding the monthly sales per SKU, along with their price and cost.\n",
    "\n",
    "### Part 1\n",
    "0 points possible (ungraded)\n",
    "What is the least profitable of the type A products? Please, write down its SKU in the answer box.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_excel('w1pp3_sku3ms.xls', names= ['sku', 'cost', 'unit_price', 'avg_units_sold_monthly'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['profit'] = (data['unit_price'] - data['cost'])  * data['avg_units_sold_monthly']\n",
    "data.sort_values(by=['profit'], ascending=False, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['profit_cumsum'] = data['profit'].cumsum()\n",
    "data['profit_percent'] = data['profit_cumsum'] / data['profit'].sum()\n",
    "\n",
    "def categorize(pp):\n",
    "    '''\n",
    "    pp: profit_percent: \n",
    "    typeA: <=34%, \n",
    "    typeB: >34% & <=(34+37)%\n",
    "    typeC: > (34+37)%\n",
    "    '''\n",
    "    if pp <= 0.34:\n",
    "        return 'A'\n",
    "    elif pp <= 0.34+0.37:\n",
    "        return 'B'\n",
    "    else: \n",
    "        return 'C'\n",
    "    \n",
    "data['type'] = data['profit_percent'].apply(categorize)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sku</th>\n",
       "      <th>cost</th>\n",
       "      <th>unit_price</th>\n",
       "      <th>avg_units_sold_monthly</th>\n",
       "      <th>sales</th>\n",
       "      <th>profit</th>\n",
       "      <th>profit_cumsum</th>\n",
       "      <th>profit_percent</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>SKU-3MS-559570</td>\n",
       "      <td>1003.82</td>\n",
       "      <td>2192.81</td>\n",
       "      <td>10.0</td>\n",
       "      <td>21928.1</td>\n",
       "      <td>11889.9</td>\n",
       "      <td>202023.038</td>\n",
       "      <td>0.337306</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               sku     cost  unit_price  avg_units_sold_monthly    sales  \\\n",
       "12  SKU-3MS-559570  1003.82     2192.81                    10.0  21928.1   \n",
       "\n",
       "     profit  profit_cumsum  profit_percent type  \n",
       "12  11889.9     202023.038        0.337306    A  "
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[data['type'] == 'A'].tail(n=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Part 2\n",
    "0 points possible (ungraded)\n",
    "What type of product is SKU-3MS-507011?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sku</th>\n",
       "      <th>cost</th>\n",
       "      <th>unit_price</th>\n",
       "      <th>avg_units_sold_monthly</th>\n",
       "      <th>sales</th>\n",
       "      <th>profit</th>\n",
       "      <th>profit_cumsum</th>\n",
       "      <th>profit_percent</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>SKU-3MS-507011</td>\n",
       "      <td>845.18</td>\n",
       "      <td>1905.95</td>\n",
       "      <td>7.3</td>\n",
       "      <td>13913.435</td>\n",
       "      <td>7743.621</td>\n",
       "      <td>355427.201</td>\n",
       "      <td>0.593436</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               sku    cost  unit_price  avg_units_sold_monthly      sales  \\\n",
       "28  SKU-3MS-507011  845.18     1905.95                     7.3  13913.435   \n",
       "\n",
       "      profit  profit_cumsum  profit_percent type  \n",
       "28  7743.621     355427.201        0.593436    B  "
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[data['sku'] == 'SKU-3MS-507011']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## PP3: Cakes at the Bagel Shop\n",
    " Bookmark this page\n",
    "\n",
    "Practice Problem 3: Cakes at the Bagel shop\n",
    "\n",
    "At the bagel shop, you have some cakes that you sell that are pretty slow movers.  The chocolate cake, for example, only sells on average 2.5 cakes per week.  You have noticed that the demand follows a Poisson distribution. \n",
    "\n",
    "### Part 1\n",
    "0 points possible (ungraded)\n",
    "What is the probability that you will sell more than 3 cakes in a week?\n",
    "\n",
    "I am too lazy to hand calculate, use the built in package from [scipy.stats.poisson](https://docs.scipy.org/doc/scipy/reference/generated/scipy.stats.poisson.html)\n",
    "\n",
    "\n",
    "\"A probability mass function differs from a probability density function (pdf) in that the latter is associated with continuous rather than discrete random variables; the values of the probability density function are not probabilities as such: a pdf must be integrated over an interval to yield a probability.\" - Wikipedia"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.24242386686693385"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "k = 3\n",
    "mu = 2.5\n",
    "1- scipy.stats.poisson.cdf(k, mu)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Part 2\n",
    "0 points possible (ungraded)\n",
    "\n",
    "(Enter your solution as a probability between 0 and 1 rather than as a percentage)\n",
    "What is the probability that you will sell no cakes in a week?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0820849986238988"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scipy.stats.poisson.cdf(0, mu)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Part 3\n",
    "0 points possible (ungraded)\n",
    "\n",
    "Suppose you have 5 cakes made ready to sell. What is the probability that you will sell out?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 0.20521249656\n",
      "2 0.2565156207\n",
      "3 0.21376301725\n",
      "4 0.133601885781\n",
      "5 0.0668009428905\n",
      "6 0.0668009428905\n",
      ">>> 0.124106036819\n"
     ]
    }
   ],
   "source": [
    "p = 0\n",
    "for i in range(1, 7):\n",
    "    if i <= 5:\n",
    "        prob = scipy.stats.poisson.pmf(i, mu)\n",
    "        p += prob\n",
    "    print(i, prob)\n",
    "  \n",
    "print('>>>', 1- p)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## PP4: Zaragoza Auto Supply\n",
    " Bookmark this page\n",
    " \n",
    "Practice Problem 4: Zaragoza Auto Supply\n",
    "The Zaragoza Auto Supply (ZAS) company is located in Zaragoza Spain near the largest logistics hub in Europe (PLAZA) and one of the largest automobile manufacturing plants.  ZAS provides replacement parts to various auto repair dealerships across the state of Aragon and beyond.  The company has historically managed all of its parts in an identical fashion.  However, a recent graduate of the Zaragoza Logistics Center (ZLC) Master's Program has been hired to manage the supply chain.  She believes that the parts should be segmented and has asked you to run an ABC analysis on a sample of 50 of ZAS's parts.\n",
    "\n",
    "In the linked file (w1pp6_ZaragozaAutoSupply_Data.xls), you will find the information you need regarding the weekly sales volume, weekly sales standard deviation, unit cost, and unit sales price for each SKU.  All currencies are in Euros.\n",
    "\n",
    "Please enter all your numerical answers with at least 4 significant figures.\n",
    "\n",
    "### Part 1\n",
    "0 points possible (ungraded)\n",
    "\n",
    "Let's start by getting comfortable with the data set. Which SKU has the highest average weekly cost?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sku</th>\n",
       "      <th>avg_weekly_sales</th>\n",
       "      <th>std_weekly_sales</th>\n",
       "      <th>cost</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A48</td>\n",
       "      <td>172</td>\n",
       "      <td>41.29</td>\n",
       "      <td>27.73</td>\n",
       "      <td>38.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AA872</td>\n",
       "      <td>33</td>\n",
       "      <td>8.81</td>\n",
       "      <td>73.44</td>\n",
       "      <td>83.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>B842</td>\n",
       "      <td>8</td>\n",
       "      <td>2.94</td>\n",
       "      <td>110.40</td>\n",
       "      <td>186.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BB806</td>\n",
       "      <td>48</td>\n",
       "      <td>4.04</td>\n",
       "      <td>55.00</td>\n",
       "      <td>75.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>C161</td>\n",
       "      <td>3</td>\n",
       "      <td>0.95</td>\n",
       "      <td>60.60</td>\n",
       "      <td>98.67</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     sku  avg_weekly_sales  std_weekly_sales    cost   price\n",
       "0    A48               172             41.29   27.73   38.35\n",
       "1  AA872                33              8.81   73.44   83.62\n",
       "2   B842                 8              2.94  110.40  186.35\n",
       "3  BB806                48              4.04   55.00   75.04\n",
       "4   C161                 3              0.95   60.60   98.67"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_excel('w1pp6_ZaragozaAutoSupply_Data.xls', \n",
    "                     names=['sku','avg_weekly_sales', 'std_weekly_sales', 'cost', 'price'])\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['average_weekly_cost'] = data['avg_weekly_sales'] * data['cost']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sku</th>\n",
       "      <th>avg_weekly_sales</th>\n",
       "      <th>std_weekly_sales</th>\n",
       "      <th>cost</th>\n",
       "      <th>price</th>\n",
       "      <th>average_weekly_cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>II762</td>\n",
       "      <td>117</td>\n",
       "      <td>4.06</td>\n",
       "      <td>49.92</td>\n",
       "      <td>52.53</td>\n",
       "      <td>5840.64</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      sku  avg_weekly_sales  std_weekly_sales   cost  price  \\\n",
       "17  II762               117              4.06  49.92  52.53   \n",
       "\n",
       "    average_weekly_cost  \n",
       "17              5840.64  "
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[data['average_weekly_cost'] == data['average_weekly_cost'].max()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Part 2\n",
    "0 points possible (ungraded)\n",
    "\n",
    "Which SKU has the highest average weekly revenue? Weekly revenue is defined as AverageWeeklySales*UnitSalesPrice"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sku</th>\n",
       "      <th>avg_weekly_sales</th>\n",
       "      <th>std_weekly_sales</th>\n",
       "      <th>cost</th>\n",
       "      <th>price</th>\n",
       "      <th>average_weekly_cost</th>\n",
       "      <th>average_weekly_revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>K261</td>\n",
       "      <td>212</td>\n",
       "      <td>113.95</td>\n",
       "      <td>23.76</td>\n",
       "      <td>40.11</td>\n",
       "      <td>5037.12</td>\n",
       "      <td>8503.32</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     sku  avg_weekly_sales  std_weekly_sales   cost  price  \\\n",
       "20  K261               212            113.95  23.76  40.11   \n",
       "\n",
       "    average_weekly_cost  average_weekly_revenue  \n",
       "20              5037.12                 8503.32  "
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['average_weekly_revenue'] = data['avg_weekly_sales'] * data['price']\n",
    "data[data['average_weekly_revenue'] == data['average_weekly_revenue'].max()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Part 3\n",
    "0 points possible (ungraded)\n",
    "\n",
    "Which SKU has the highest percent profit margin (profit/cost) per unit?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sku</th>\n",
       "      <th>avg_weekly_sales</th>\n",
       "      <th>std_weekly_sales</th>\n",
       "      <th>cost</th>\n",
       "      <th>price</th>\n",
       "      <th>average_weekly_cost</th>\n",
       "      <th>average_weekly_revenue</th>\n",
       "      <th>percent_profit_margin</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Q61</td>\n",
       "      <td>4</td>\n",
       "      <td>1.15</td>\n",
       "      <td>78.4</td>\n",
       "      <td>139.4</td>\n",
       "      <td>313.6</td>\n",
       "      <td>557.6</td>\n",
       "      <td>0.778061</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    sku  avg_weekly_sales  std_weekly_sales  cost  price  average_weekly_cost  \\\n",
       "32  Q61                 4              1.15  78.4  139.4                313.6   \n",
       "\n",
       "    average_weekly_revenue  percent_profit_margin  \n",
       "32                   557.6               0.778061  "
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['percent_profit_margin'] = (data['price'] - data['cost'] )/ data['cost']\n",
    "data[data['percent_profit_margin'] == data['percent_profit_margin'].max()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Part 4\n",
    "0 points possible (ungraded)\n",
    "\n",
    "Which SKU has the highest average weekly profit? Profit is defined as AverageWeeklySales*(UnitSalesPrice-UnitCost)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sku</th>\n",
       "      <th>avg_weekly_sales</th>\n",
       "      <th>std_weekly_sales</th>\n",
       "      <th>cost</th>\n",
       "      <th>price</th>\n",
       "      <th>average_weekly_cost</th>\n",
       "      <th>average_weekly_revenue</th>\n",
       "      <th>percent_profit_margin</th>\n",
       "      <th>average_weekly_profit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>K261</td>\n",
       "      <td>212</td>\n",
       "      <td>113.95</td>\n",
       "      <td>23.76</td>\n",
       "      <td>40.11</td>\n",
       "      <td>5037.12</td>\n",
       "      <td>8503.32</td>\n",
       "      <td>0.688131</td>\n",
       "      <td>3466.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     sku  avg_weekly_sales  std_weekly_sales   cost  price  \\\n",
       "20  K261               212            113.95  23.76  40.11   \n",
       "\n",
       "    average_weekly_cost  average_weekly_revenue  percent_profit_margin  \\\n",
       "20              5037.12                 8503.32               0.688131   \n",
       "\n",
       "    average_weekly_profit  \n",
       "20                 3466.2  "
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['average_weekly_profit'] = data['average_weekly_revenue'] - data['average_weekly_cost']\n",
    "data[data['average_weekly_profit'] == data['average_weekly_profit'].max()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Part 5\n",
    "0 points possible (ungraded)\n",
    "\n",
    "Which SKU has the most volatile demand in terms of coefficient of variation? The coefficient of variation is defined as CV=σμ."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sku</th>\n",
       "      <th>avg_weekly_sales</th>\n",
       "      <th>std_weekly_sales</th>\n",
       "      <th>cost</th>\n",
       "      <th>price</th>\n",
       "      <th>average_weekly_cost</th>\n",
       "      <th>average_weekly_revenue</th>\n",
       "      <th>percent_profit_margin</th>\n",
       "      <th>average_weekly_profit</th>\n",
       "      <th>cv</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>L977</td>\n",
       "      <td>4</td>\n",
       "      <td>7.23</td>\n",
       "      <td>53.02</td>\n",
       "      <td>72.32</td>\n",
       "      <td>212.08</td>\n",
       "      <td>289.28</td>\n",
       "      <td>0.364014</td>\n",
       "      <td>77.2</td>\n",
       "      <td>1.8075</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     sku  avg_weekly_sales  std_weekly_sales   cost  price  \\\n",
       "22  L977                 4              7.23  53.02  72.32   \n",
       "\n",
       "    average_weekly_cost  average_weekly_revenue  percent_profit_margin  \\\n",
       "22               212.08                  289.28               0.364014   \n",
       "\n",
       "    average_weekly_profit      cv  \n",
       "22                   77.2  1.8075  "
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['cv'] = data['std_weekly_sales'] / data['avg_weekly_sales']\n",
    "data[data['cv'] == data['cv'].max()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Part 6\n",
    "0 points possible (ungraded)\n",
    "\n",
    "You want to get a handle on how the A items might be distributed by total weekly profitability.\n",
    "\n",
    "What percentage of the total profit is contributed by the top 20% of the 50 SKUs (by profit)? Just enter a value and do not include the % sign. For example, if you think the value is 35.5%, just enter 35.5:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.7343084300023995"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.sort_values(by=['average_weekly_profit'], ascending=False, inplace=True)\n",
    "top_20 = data[:int(data.shape[0]*0.2)]\n",
    "top_20['average_weekly_profit'].sum() / data['average_weekly_profit'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What percentage of the total profit is contributed by the bottom 50% of the 50 SKUs, by profitability? Just enter the value and do not include the % sign."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.07176381949743098"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idx = int(data.shape[0]*0.5)\n",
    "bot_50 = data[idx:]\n",
    "bot_50['average_weekly_profit'].sum() / data['average_weekly_profit'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Part 7\n",
    "0 points possible (ungraded)\n",
    "\n",
    "You believe that the fast moving, highly volatile SKUs should be managed separately with more time spent on improving the forecasting capabilities. You select the criteria for fast moving to be selling 50 or more units per week and the criteria for highly volatile as having a CV greater than or equal to 0.50. Note.\n",
    "\n",
    "What percentage of the total profit is contributed by these Fast Moving AND Highly Volatile SKUs (both criteria satisfied)? Just enter a value and do not include the % sign. For example, if you think the value is 35.5%, just enter 35.5:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sku</th>\n",
       "      <th>avg_weekly_sales</th>\n",
       "      <th>std_weekly_sales</th>\n",
       "      <th>cost</th>\n",
       "      <th>price</th>\n",
       "      <th>average_weekly_cost</th>\n",
       "      <th>average_weekly_revenue</th>\n",
       "      <th>percent_profit_margin</th>\n",
       "      <th>average_weekly_profit</th>\n",
       "      <th>cv</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>K261</td>\n",
       "      <td>212</td>\n",
       "      <td>113.95</td>\n",
       "      <td>23.76</td>\n",
       "      <td>40.11</td>\n",
       "      <td>5037.12</td>\n",
       "      <td>8503.32</td>\n",
       "      <td>0.688131</td>\n",
       "      <td>3466.20</td>\n",
       "      <td>0.537500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>RR621</td>\n",
       "      <td>100</td>\n",
       "      <td>33.38</td>\n",
       "      <td>28.20</td>\n",
       "      <td>47.59</td>\n",
       "      <td>2820.00</td>\n",
       "      <td>4759.00</td>\n",
       "      <td>0.687589</td>\n",
       "      <td>1939.00</td>\n",
       "      <td>0.333800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>UU885</td>\n",
       "      <td>60</td>\n",
       "      <td>65.00</td>\n",
       "      <td>57.98</td>\n",
       "      <td>88.96</td>\n",
       "      <td>3478.80</td>\n",
       "      <td>5337.60</td>\n",
       "      <td>0.534322</td>\n",
       "      <td>1858.80</td>\n",
       "      <td>1.083333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>F948</td>\n",
       "      <td>15</td>\n",
       "      <td>4.52</td>\n",
       "      <td>160.50</td>\n",
       "      <td>283.43</td>\n",
       "      <td>2407.50</td>\n",
       "      <td>4251.45</td>\n",
       "      <td>0.765919</td>\n",
       "      <td>1843.95</td>\n",
       "      <td>0.301333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A48</td>\n",
       "      <td>172</td>\n",
       "      <td>41.29</td>\n",
       "      <td>27.73</td>\n",
       "      <td>38.35</td>\n",
       "      <td>4769.56</td>\n",
       "      <td>6596.20</td>\n",
       "      <td>0.382979</td>\n",
       "      <td>1826.64</td>\n",
       "      <td>0.240058</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      sku  avg_weekly_sales  std_weekly_sales    cost   price  \\\n",
       "20   K261               212            113.95   23.76   40.11   \n",
       "35  RR621               100             33.38   28.20   47.59   \n",
       "40  UU885                60             65.00   57.98   88.96   \n",
       "10   F948                15              4.52  160.50  283.43   \n",
       "0     A48               172             41.29   27.73   38.35   \n",
       "\n",
       "    average_weekly_cost  average_weekly_revenue  percent_profit_margin  \\\n",
       "20              5037.12                 8503.32               0.688131   \n",
       "35              2820.00                 4759.00               0.687589   \n",
       "40              3478.80                 5337.60               0.534322   \n",
       "10              2407.50                 4251.45               0.765919   \n",
       "0               4769.56                 6596.20               0.382979   \n",
       "\n",
       "    average_weekly_profit        cv  \n",
       "20                3466.20  0.537500  \n",
       "35                1939.00  0.333800  \n",
       "40                1858.80  1.083333  \n",
       "10                1843.95  0.301333  \n",
       "0                 1826.64  0.240058  "
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.3272584973701894"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flag_fast_moving = (data['avg_weekly_sales'] >= 50)\n",
    "flag_high_volatile = (data['cv'] >= 0.5)\n",
    "num = data[flag_fast_moving & flag_high_volatile]['average_weekly_profit'].sum()\n",
    "den = data['average_weekly_profit'].sum()\n",
    "num/den"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## PP5: Forecasting Ordroid Demand\n",
    " Bookmark this page\n",
    " \n",
    "Practice Problem 5: Forecasting Ordroid Devices\n",
    "You have just been hired by a company that manufactures mid-range communication devices that use the Ordroid open source operating system.  The company is focused on innovating its products and has not put much thought on its inventory or forecasting capabilities.   Your boss thinks there might be a problem in the forecasting of the Ordroid Devices and wants you to figure it out.  The Ordroid, far from being new to the market, has been out for two years. \n",
    "\n",
    "Knowing this, you have asked for data on both years of historical sales as well as any forecasts, promotions, pricing changes, or competitive analyses made during this time.  Your boss laughs and provides you with all the data they have: the last six months of sales.  You ask to meet with the current demand planner for the Ordroid Devices and she tells you that they use a forecasting algorithm of her own design and there is no documentation.  \n",
    "\n",
    "Download the spreadsheets with the data here:\n",
    "\n",
    "In Excel format (link to Ordroid_Data.xlsx)\n",
    "\n",
    "In LibreOffice format (link Ordroid_Data.ods)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>month</th>\n",
       "      <th>actual</th>\n",
       "      <th>forecast</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>May</td>\n",
       "      <td>1509</td>\n",
       "      <td>1900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>June</td>\n",
       "      <td>1610</td>\n",
       "      <td>1200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>July</td>\n",
       "      <td>1708</td>\n",
       "      <td>2100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>August</td>\n",
       "      <td>1866</td>\n",
       "      <td>1300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>September</td>\n",
       "      <td>2092</td>\n",
       "      <td>2500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       month  actual  forecast\n",
       "0        May    1509      1900\n",
       "1       June    1610      1200\n",
       "2       July    1708      2100\n",
       "3     August    1866      1300\n",
       "4  September    2092      2500"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_excel('Ordroid_Data.xlsx', names=['month', 'actual', 'forecast'])\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Part 1\n",
    "0 points possible (ungraded)\n",
    "\n",
    "As a first step, you want to calculate some different performance metrics for the small data sample. Recall that the definition of the error term is the Actual demand minus the Forecasted demand.\n",
    "\n",
    "What is the mean deviation of the forecasts in this data sample?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "112.5"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['error'] = data['actual'] - data['forecast']\n",
    "mean_deviation = data['error'].mean()\n",
    "mean_deviation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Part 2\n",
    "0 points possible (ungraded)\n",
    "\n",
    "What is the mean absolute deviation (MAD) of the forecasts in this data sample?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "509.5"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "mean_absolute_devication = np.abs(data['error']).mean()\n",
    "mean_absolute_devication"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Part 3\n",
    "0 points possible (ungraded)\n",
    "\n",
    "What is the root mean square error (RMSE) of the forecasts in this data sample?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "540.61153644121703"
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "root_mean_squared_error = np.sqrt((data['error']**2).sum() / len(data['error']))\n",
    "root_mean_squared_error"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Part 4\n",
    "0 points possible (ungraded)\n",
    "\n",
    "What is the mean percent error (MPE) of the forecasts in this data sample? Just enter the number and not the percentage sign, e.g., for 19.7% enter 19.7."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.04111951090720982"
      ]
     },
     "execution_count": 145,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mean_percent_error = (data['error'] / data['actual']).mean()\n",
    "mean_percent_error"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Part 5\n",
    "0 points possible (ungraded)\n",
    "\n",
    "What is the mean absolute percent error (MAPE) of the forecasts in this data sample? Just enter the number and not the percentage sign, e.g., for 19.7% enter 19.7."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.2690024682765844"
      ]
     },
     "execution_count": 147,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mean_absolute_percent_error = (np.abs(data['error'] / data['actual'])).mean()\n",
    "mean_absolute_percent_error"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
